package sg.edu.nus.iss.phoenix.schedule.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import sg.edu.nus.iss.phoenix.core.dao.DBConstants;
import sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO;
import sg.edu.nus.iss.phoenix.schedule.entity.ProgramSlot;
import sg.edu.nus.iss.phoenix.schedule.entity.Schedule;
import sg.edu.nus.iss.phoenix.schedule.service.DateUtil;
import sg.edu.nus.iss.phoenix.user.entity.User;
/**
 * Schedule Data Access Object (DAO). This class contains all database
 * handling that is needed to permanently store and retrieve Schedule or Program Slot object
 * instances.
 * @author Ramprasad, Karthic Kumar, Goh Zhe Yi
 */


public class ScheduleDAOImpl implements ScheduleDAO {
	Connection connection;

	public ScheduleDAOImpl() {
		super();
		// TODO Auto-generated constructor stub
		connection = openConnection();
	}

	private Connection openConnection() {
		Connection conn = null;

		try {
			Class.forName(DBConstants.COM_MYSQL_JDBC_DRIVER);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			conn = DriverManager.getConnection(DBConstants.dbUrl,
					DBConstants.dbUserName, DBConstants.dbPassword);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	private void closeConnection() {
		try {
			this.connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * databaseUpdate-method. This method is a helper method for internal use.
	 * It will execute all database handling that will change the information in
	 * tables. SELECT queries will not be executed here however. The return
	 * value indicates how many rows were affected. This method will also make
	 * sure that if cache is used, it will reset when data changes.
	 * 
	 * @param conn
	 *            This method requires working database connection.
	 * @param stmt
	 *            This parameter contains the SQL statement to be excuted.
	 */
	protected int databaseUpdate(PreparedStatement stmt) throws SQLException {

		int result = stmt.executeUpdate();

		return result;
	}

	@Override
	public int createSchedule(Schedule schedule) throws SQLException {

		ArrayList<ProgramSlot> programSlotList = schedule.getProgramSlotList();

		int rowcount = 0;
		PreparedStatement stmt = null;
		String sql = "";
		connection = openConnection();
		try {
			for (ProgramSlot ps : programSlotList) {
				sql = "INSERT INTO `program-slot` (`dateOfProgram`, `duration`, `program-name`, `startTime`) VALUES (?,?,?,?); ";
				stmt = connection.prepareStatement(sql);
				stmt.setString(1, ps.getDateOfProgram());
				stmt.setString(2, ps.getDuration());
				stmt.setString(3, ps.getProgramName());
				stmt.setString(4, ps.getStartTime());
				rowcount = rowcount + databaseUpdate(stmt);
			}
		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
		}
		return rowcount;
	}

	@Override
	public int modifySchedule(Schedule schedule) throws SQLException {

		this.deleteSchedule(schedule);
		return this.createSchedule(schedule);
	}

	@Override
	public int deleteSchedule(Schedule schedule) throws SQLException {

		String sql = "DELETE FROM `program-slot` WHERE (`dateOfProgram` = ? );  ";
		PreparedStatement stmt = null;

		int rowcount = 0;
		connection = openConnection();
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setString(1, schedule.getDateOfSchedule());

			rowcount = databaseUpdate(stmt);

		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
		}
		return rowcount;
	}

	@Override
	public ArrayList<Schedule> getListOfSchedule(Date startDate, Date endDate)
			throws Exception {
		ArrayList<Schedule> scheduleList = new ArrayList<Schedule>();

		do {
			scheduleList.add(this.getSchedule(startDate.toString()));
			startDate = DateUtil.addDays(startDate, 1);
		} while (startDate.before(endDate));

		return scheduleList;
	}

	@Override
	public int getScheduleCountBetweenStartAndEndDate(String startDate, String endDate) throws SQLException {
		int resultCount = 0;
		ResultSet result = null;
		String sql = "select count(*) as recordCount from phoenix.`program-slot`" +
				" where ?  between starttime and addtime(starttime, duration) " +
				" or ? between starttime and addtime(starttime, duration) " + 
				" or starttime between ? and ? " + 
				" or addtime(starttime, duration) between ? and ?;";
		PreparedStatement stmt = null;
		connection = openConnection();
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setString(1, startDate);
			stmt.setString(2, endDate);
			stmt.setString(3, startDate);
			stmt.setString(4, endDate);
			stmt.setString(5, startDate);
			stmt.setString(6, endDate);
			result = stmt.executeQuery();
			while (result.next()) {
				resultCount = result.getInt("recordCount");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
			closeConnection();
		}

		return resultCount;
	}

	@Override
	public Schedule getSchedule(String startDate) throws Exception {
		ArrayList<ProgramSlot> searchResults = new ArrayList<ProgramSlot>();
		ResultSet result = null;
		String sql = "SELECT ps.dateOfProgram, ps.duration, ps.`program-name`, ps.programslotid ,ps.startTime , pre.id as presenterid, pre.name as presentername, pro.id  as producerid, pro.name  as producername FROM `program-slot` ps 	inner join `user` pre on ps.presenterId = pre.id 	inner join `user` pro on ps.producerId = pro.id where ps.dateOfProgram = ? ;";
		PreparedStatement stmt = null;
		connection = openConnection();
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setString(1, DateUtil.convertToMySQLDateFormat(startDate));
			result = stmt.executeQuery();
			while (result.next()) {
				ProgramSlot temp = new ProgramSlot();
				temp.setDateOfProgram(DateUtil.convertToJavaDateFormat(result.getDate("dateOfProgram")
						.toString()));
				temp.setDuration(result.getTime("duration").toString());
				temp.setProgramName(result.getString("program-name"));
				temp.setProgramSlotId(result.getInt("programslotid"));
				temp.setStartTime(result.getDate("startTime").toString() + " " + result.getTime("startTime").toString());

				User presenter = new User();
				presenter.setId(result.getString("presenterid"));
				presenter.setName(result.getString("presentername"));

				User producer = new User();
				producer.setId(result.getString("producerid"));
				producer.setName(result.getString("producername"));
				
				temp.setPresenter(presenter);
				temp.setProducer(producer);
				
				searchResults.add(temp);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
			closeConnection();
		}

		Schedule schedule = new Schedule();
		schedule.setDateOfSchedule(startDate);
		schedule.setProgramSlotList(searchResults);

		return schedule;

	}

	@Override
	public void createProgramSlot(ProgramSlot programSlot) throws SQLException {
		// TODO Auto-generated method stub
		 int rowcount = 0;
		  PreparedStatement stmt = null;
		  String sql = "";
		  connection = openConnection();
		  /*Sample Query
		   * INSERT INTO `phoenix`.`program-slot` 
		   *  (`duration`, `dateOfProgram`, `startTime`, `program-name`, `presenterId`, `producerId`) 
		   *  VALUES ('03:00:00', '2012-09-27', '2012-09-27 20:20:00', 'ppk', 'victor', 'dogbert' );
		   */
		  sql = "INSERT INTO `program-slot` (`duration`, `dateOfProgram`, `program-name`, " +
		    "`startTime`, `presenterId`, `producerId`) VALUES (?,?,?,?,?,?); ";

		  try {
		   stmt = connection.prepareStatement(sql);
		   stmt.setString(1, programSlot.getDuration());
		   stmt.setString(2, programSlot.getDateOfProgram());
		   stmt.setString(3, programSlot.getProgramName());
		   stmt.setString(4, programSlot.getStartTime());
		   stmt.setString(5, programSlot.getPresenter().getId());
		   stmt.setString(6, programSlot.getProducer().getId());
		   rowcount = rowcount + databaseUpdate(stmt);
		  } catch (SQLException e) {
		   // TODO Auto-generated catch block
		   e.printStackTrace();
		  } finally {
		   if (stmt != null)
		    stmt.close();
		   closeConnection();
		  }

	}
	
	
	public void modifyProgramSlot(ProgramSlot programSlot) throws SQLException {
		// TODO Auto-generated method stub
		
		 int rowcount = 0;
		  PreparedStatement stmt = null;
		  String sql = "";
		  connection = openConnection();
		  /*Sample Query
		   * INSERT INTO `phoenix`.`program-slot` 
		   *  (`duration`, `dateOfProgram`, `startTime`, `program-name`, `presenterId`, `producerId`) 
		   *  VALUES ('03:00:00', '2012-09-27', '2012-09-27 20:20:00', 'ppk', 'victor', 'dogbert' );
		   */
		  
		 sql =  "UPDATE `program-slot` SET " +
		 		"`duration`=?, `dateOfProgram`=?, `startTime`=?, `program-name`=?, " + 
				 "`presenterId`=?, `producerId`=? WHERE `programslotid`=? ;";
		 /* sql = "UPDATE INTO `program-slot` SET (`duration`, `dateOfProgram`, `program-name`, " +
		    "`startTime`, `presenterId`, `producerId`) VALUES (?,?,?,?,?,?); ";*/
		 
		  try {
		   stmt = connection.prepareStatement(sql);
		   stmt.setString(1, programSlot.getDuration());
		   stmt.setString(2, programSlot.getDateOfProgram());
		   stmt.setString(3,  programSlot.getStartTime());
		   stmt.setString(4,programSlot.getProgramName() );
		   stmt.setString(5, programSlot.getPresenter().getId());
		   stmt.setString(6, programSlot.getProducer().getId());
		   stmt.setInt(7, programSlot.getProgramSlotId());
		   rowcount = rowcount + databaseUpdate(stmt);
		  } catch (SQLException e) {
		   // TODO Auto-generated catch block
		   e.printStackTrace();
		  } finally {
		   if (stmt != null)
		    stmt.close();
		   closeConnection();
		  }

	}
	
	public void deleteProgramSlot(int programSlotId) throws SQLException {
		// TODO Auto-generated method stub
	
		String sql = "Delete from `program-slot` where startTime = " +
				"(Select sTime from " +
				"(select startTime as sTime from `program-slot` as temp where temp.programslotid = ?) as temp2);";

		PreparedStatement stmt = null;

		int rowcount = 0;
		connection = openConnection();
		try {
			stmt = connection.prepareStatement(sql);
			stmt.setInt(1, programSlotId);

			rowcount = databaseUpdate(stmt);

		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
		}
		
		
	}
}
